package org.hotpotmaterial.code.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.hotpotmaterial.code.entity.PartyProjectPO;
import org.hotpotmaterial.code.entity.ProjectPO;

/**
 * @author wenxing
 *
 */
public interface ProjectRepository extends JpaRepository<ProjectPO, String>, JpaSpecificationExecutor<ProjectPO> {

	/**
	 * 根据项目id删除项目
	 * 
	 * @param projectId
	 * @param delFlag
	 */
	@Transactional
	@Modifying
	@Query(value = "UPDATE ProjectPO p SET p.delFlag = :delFlag WHERE p.id = :id")
	void updateByProjectId(@Param("id") String id, @Param("delFlag") String delFlag);

	/**
	 * 根据id查询项目
	 * 
	 * @param projectId
	 * @param delFlag
	 */
	ProjectPO findByIdAndDelFlag(String id, String delFlag);

	/**
	 * 根据项目id修改项目星标识
	 * 
	 * @param projectId
	 * @param delFlag
	 */
	@Transactional
	@Modifying
	@Query(value = "UPDATE ProjectPO p SET p.flag = :flag WHERE p.id = :id")
	void updateStarByProjectIdAndFlag(@Param("id") String id, @Param("flag") String flag);

	@Query(value = "SELECT temp.datemonth AS mon,COUNT(id) AS num FROM (SELECT id,LEFT(created_at,7) as datemonth FROM project ) AS temp WHERE datemonth>?1 GROUP BY temp.datemonth", nativeQuery = true)
	Collection<?> findMonthData(String date);

	@Query(value = "SELECT COUNT(0) FROM project WHERE LOCATE('uiConfigEnabled',components)>0", nativeQuery = true)
	long findUiCount();

	@Query(value = "SELECT\r\n" + 
			"	t1.user_name AS userName,\r\n" + 
			"	t1.user_id AS userID,\r\n" + 
			"	t1.loginCount AS loginCount,\r\n" + 
			"	t1.dep,\r\n" + 
			"	t1.jobNo,\r\n" + 
			"	IFNULL(t2.projectCount, 0) AS projectCount\r\n" + 
			"FROM\r\n" + 
			"	(\r\n" + 
			"		SELECT\r\n" + 
			"			user_id,\r\n" + 
			"			user_name,\r\n" + 
			"			ANY_VALUE (dep_full_name) AS dep,\r\n" + 
			"			ANY_VALUE (user_login_id) AS jobNo,\r\n" + 
			"			COUNT(0) AS loginCount\r\n" + 
			"		FROM\r\n" + 
			"			login_info\r\n" + 
			"		WHERE \r\n" + 
			"			created_at LIKE ?1\r\n" + 
			"		GROUP BY\r\n" + 
			"			user_id,\r\n" + 
			"			user_name\r\n" + 
			"	) AS t1\r\n" + 
			"LEFT JOIN (\r\n" + 
			"	SELECT\r\n" + 
			"		party_id,\r\n" + 
			"		COUNT(0) AS projectCount\r\n" + 
			"	FROM\r\n" + 
			"		party_project\r\n" + 
			"	GROUP BY\r\n" + 
			"		party_id\r\n" + 
			") AS t2 ON t1.user_id = t2.party_id\r\n" + 
			"ORDER BY\r\n" + 
			"	t1.loginCount DESC\r\n" + 
			"LIMIT 5", nativeQuery = true)
	Collection<?> findActiveTop5(String dateMonth);
}
